package br.great.DAO;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import br.great.classes.TexteRange;
import br.great.beans.AnnotationPhotoInfo;

/**
* PhotoMap
* Copyright (c) 2007-2008 STEAMER - LIG, All rights reserved.
*/

/**
 * @author Developpement of PhotoMap
 */
public class DAO_AnnotationPhotoInfo {
    int idYears;
    int idMonths;
    int idDays;
    int idSeason;
    int idPartOfDay;
    int idTemperature;
    int idWeather;
    int idCountry;
    int idCity;
    int idRoad;
    int idAddress;
    int idTime;
    
    private static volatile DAO_AnnotationPhotoInfo instaceDAO_AnnotationPhotoInfo = new DAO_AnnotationPhotoInfo();
    
    /** The private constructor of DAO_AnnotationPhotoInfo for singleton
    */
    private DAO_AnnotationPhotoInfo(){    
    }
    
    /** The method for get the instance of the class DAO_AnnotationPhotoInfo
    * @return DAO_AnnotationPhotoInfo instaceDAO_AnnotationPhotoInfo
    */
    public static DAO_AnnotationPhotoInfo getInstance() {
        //if(instaceDAO_AnnotationPhotoInfo==null)
           synchronized(DAO_AnnotationPhotoInfo.class) {
             if (instaceDAO_AnnotationPhotoInfo == null)
               instaceDAO_AnnotationPhotoInfo = new DAO_AnnotationPhotoInfo();
           }
        return instaceDAO_AnnotationPhotoInfo;
    }
    
    public int[] insertAnnotationPhotoInfo(AnnotationPhotoInfo annotationPhotoInfo){
        // get tag for looking for the tag is existe or not
        String getYears = "select * from Years where Years='"+annotationPhotoInfo.getYEARS()+"';";
        String getMonths = "select * from Months where Months='"+annotationPhotoInfo.getMONTHS()+"';";
        String getDays = "select * from Days where Days='"+annotationPhotoInfo.getDAYS()+"';";
        String getSeason = "select * from Season where Lower(Season)=Lower('"+annotationPhotoInfo.getSEASON()+"');";
        String getPart_Of_Day = "select * from Part_Of_Day where Lower(Part_Of_Day)=Lower('"+annotationPhotoInfo.getPART_OF_DAY()+"');";
        String getTemperature = "select * from Temperature where value='"+annotationPhotoInfo.getVALUE()+"';";
        String getWeather = "select * from Weather where Lower(Weather)=Lower('"+annotationPhotoInfo.getWEATHER()+"');";
        String getCountry = "select * from Country where Lower(CODE_Country)=Lower('"+TexteRange.tradeText(annotationPhotoInfo.getNAME_COUNTRY())+"');";
        String getCity = "select * from City where Lower(name_City) =Lower('"+TexteRange.tradeText(annotationPhotoInfo.getNAME_CITY())+"');";
       String getRoad = "select * from Road where Lower(name_Road)=Lower('"+TexteRange.tradeText(annotationPhotoInfo.getNAME_ROAD())+"');";
        
        // insert in a new value
        String insertYears = "insert into Years values(nextval('seqmax_years'),'"+annotationPhotoInfo.getYEARS()+"');";
        String insertMonths = "insert into Months values(nextval('seqmax_months'),'"+annotationPhotoInfo.getMONTHS()+"');";
        String insertDays = "insert into Days values(nextval('seqmax_days'),'"+annotationPhotoInfo.getDAYS()+"');";
        String insertSeason = "insert into Season values(nextval('seqmax_season'),'"+annotationPhotoInfo.getSEASON()+"');";
        String insertPart_Of_Day = "insert into Part_Of_Day values(nextval('seqmax_partOfDay'),'"+annotationPhotoInfo.getPART_OF_DAY()+"');";
        String insertTemperature = "insert into Temperature values(nextval('seqmax_temperature'),'"+annotationPhotoInfo.getVALUE()+"','"+annotationPhotoInfo.getTYPE_TEMPRETURE()+"','"+annotationPhotoInfo.isVALIDATED_TEMPERATURE()+"');";
        String insertWeather = "insert into Weather values(nextval('seqmax_weather'),'"+annotationPhotoInfo.getWEATHER()+"','"+annotationPhotoInfo.getWEATHER_ICONS()+"','"+annotationPhotoInfo.isVALIDATED_WEATHER()+"');";
//        String insertCountry = "insert into Country values(nextval('seqmax_country'),'"+annotationPhotoInfo.getNAME_COUNTRY()+"');";
        String insertCity = "insert into City values(nextval('seqmax_city'),'"+TexteRange.tradeText(annotationPhotoInfo.getNAME_CITY())+"');";
         String insertRoad = "insert into Road values(nextval('seqmax_road'),'"+TexteRange.tradeText(annotationPhotoInfo.getNAME_ROAD())+"');";
        
        // do the insert, get the value of the ID
        String getIdYears = "select max(ID_Years) from Years";
        String getIdMonths = "select max(ID_Months) from Months";
        String getIdDays = "select max(ID_Days) from Days";
        String getIdSeason = "select max(ID_Season) from Season";
        String getIdPart_Of_Day = "select max(ID_Part_Of_Day) from Part_Of_Day";
        String getIdTemperature = "select max(ID_Temperature) from Temperature";
        String getIdWeather = "select max(ID_Weather) from Weather";
        String getIdCountry = "select max(ID_Country) from Country";
        String getIdCity = "select max(ID_City) from City";
        String getIdRoad = "select max(ID_Road) from Road";
        String getIdAddress = "select max(ID_Address) from Address";
        String getIdTime = "select max(ID_Time) from Time";

        PgConnect conn = PgConnect.getInstance();
        try {
            ResultSet rs_getYears = conn.executeQuery(getYears);
            // if the tag is in the table
            if (rs_getYears.next()){
                idYears = rs_getYears.getInt("ID_Years");
            }else {
                // if th tag isn't in table, we insert the tag in it
                conn.executeInsert(insertYears);
                ResultSet rs = conn.executeQuery(getIdYears);
                while (rs.next()) {
                    idYears = rs.getInt("max");
                }
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        
        try {
            ResultSet rs_getMonths = conn.executeQuery(getMonths);
            // if the tag is in the table
            if (rs_getMonths.next()){
                idMonths = rs_getMonths.getInt("ID_Months");
            }else {
                // if th tag isn't in table, we insert the tag in it
                conn.executeInsert(insertMonths);
                ResultSet rs = conn.executeQuery(getIdMonths);
                while (rs.next()) {
                    idMonths = rs.getInt("max");
                }
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        
        try {
            ResultSet rs_getDays = conn.executeQuery(getDays);
            // if the tag is in the table
            if (rs_getDays.next()){
                idDays = rs_getDays.getInt("ID_Days");
            }else {
                // if th tag isn't in table, we insert the tag in it
                conn.executeInsert(insertDays);
                ResultSet rs = conn.executeQuery(getIdDays);
                while (rs.next()) {
                    idDays = rs.getInt("max");
                }
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        
                try {
            ResultSet rs_getSeason = conn.executeQuery(getSeason);
            // if the tag is in the table
            if (rs_getSeason.next()){
                idSeason = rs_getSeason.getInt("ID_Season");
            }else {
                // if th tag isn't in table, we insert the tag in it
                conn.executeInsert(insertSeason);
                ResultSet rs = conn.executeQuery(getIdSeason);
                while (rs.next()) {
                    idSeason = rs.getInt("max");
                }
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        
        try {
            ResultSet rs_getPartOfDay = conn.executeQuery(getPart_Of_Day);
            // if the tag is in the table
            if (rs_getPartOfDay.next()){
                idPartOfDay = rs_getPartOfDay.getInt("ID_Part_Of_Day");
            }else {
                // if th tag isn't in table, we insert the tag in it
                conn.executeInsert(insertPart_Of_Day);
                ResultSet rs = conn.executeQuery(getIdPart_Of_Day);
                while (rs.next()) {
                    idPartOfDay = rs.getInt("max");
                }
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        
        try {
            ResultSet rs_getTemperature = conn.executeQuery(getTemperature);
            // if the tag is in the table
            if (rs_getTemperature.next()){
                idTemperature = rs_getTemperature.getInt("ID_Temperature");
            }else {
                // if th tag isn't in table, we insert the tag in it
                conn.executeInsert(insertTemperature);
                ResultSet rs = conn.executeQuery(getIdTemperature);
                while (rs.next()) {
                    idTemperature = rs.getInt("max");
                }
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        
        try {
            ResultSet rs_getWeather = conn.executeQuery(getWeather);
            // if the tag is in the table
            if (rs_getWeather.next()){
                idWeather = rs_getWeather.getInt("ID_Weather");
            }else {
                // if th tag isn't in table, we insert the tag in it
                conn.executeInsert(insertWeather);
//System.out.println(insertWeather);
                ResultSet rs = conn.executeQuery(getIdWeather);
                while (rs.next()) {
                    idWeather = rs.getInt("max");
                }
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        
        try {
            ResultSet rs_getCountry = conn.executeQuery(getCountry);
            // if the tag is in the table
            if (rs_getCountry.next()){
                idCountry = rs_getCountry.getInt("ID_Country");
            }else {
                // if th tag isn't in table, we insert the tag in it
                idCountry = 0;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        
        try {
            ResultSet rs_getCity = conn.executeQuery(getCity);
            // if the tag is in the table
            if (rs_getCity.next()){
                idCity = rs_getCity.getInt("ID_City");
            }else {
                // if th tag isn't in table, we insert the tag in it
                conn.executeInsert(insertCity);
                ResultSet rs = conn.executeQuery(getIdCity);
                while (rs.next()) {
                    idCity = rs.getInt("max");
                }
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        
        try {
            ResultSet rs_getRoad = conn.executeQuery(getRoad);
            // if the tag is in the table
            if (rs_getRoad.next()){
                idRoad = rs_getRoad.getInt("ID_Road");
            }else {
                // if th tag isn't in table, we insert the tag in it
                conn.executeInsert(insertRoad);
                ResultSet rs = conn.executeQuery(getIdRoad);
                while (rs.next()) {
                    idRoad = rs.getInt("max");
                }
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        
        String insertTime = "insert into Time values (nextval('seqmax_Time'),"+idPartOfDay+","+idSeason+","+idDays+","+idMonths+","+idYears+","+annotationPhotoInfo.isVALIDATED_TIME()+",'"+annotationPhotoInfo.getDAYTIME()+"');";
        String insertAddress = "insert into Address values(nextval('seqmax_Address'),"+idRoad+","+idCountry+","+idCity+",null,"+annotationPhotoInfo.isVALIDATED_ADDRESS()+");";
        
        try {

         
            // if th tag isn't in table, we insert the tag in it
            conn.executeInsert(insertTime);
               
            ResultSet rs = conn.executeQuery(getIdTime);
            while (rs.next()) {
                idTime = rs.getInt("max");
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        
        try {
  
            // if th tag isn't in table, we insert the tag in it
            conn.executeInsert(insertAddress);
            
            ResultSet rs = conn.executeQuery(getIdAddress);
            while (rs.next()) {
                idAddress = rs.getInt("max");
            }
        } catch (SQLException ex) {
        }
        
        conn.Close();
        int[] intB = {idTemperature, idWeather, idAddress, idTime};
        return intB;
    }
    
    public String getNameCountry(String codeCountry){
        String nameCountry = new String();
        try {            
            PgConnect conn = PgConnect.getInstance();
            String getCountry = "SELECT name_country FROM country WHERE code_country='" + codeCountry + "'";
            ResultSet rs = conn.executeQuery(getCountry);
            if (rs.next()) {
                nameCountry = rs.getString("name_country");
            }
        } catch (SQLException ex) {
            Logger.getLogger(DAO_AnnotationPhotoInfo.class.getName()).log(Level.SEVERE, null, ex);
        }
        return nameCountry;
    }
//    public static void main(String[] args){
//        DAO_AnnotationPhotoInfo dapi = DAO_AnnotationPhotoInfo.getInstance();
//        AnnotationPhotoInfo api = new AnnotationPhotoInfo();
//        api.setDAYS("lundi");
//        api.setDAYTIME(Timestamp.valueOf("1999-01-08 20:38:40"));
//        api.setMONTHS("janvier");
//        api.setNAME_CITY("grenoble");
//        api.setNAME_COUNTRY("france");
//        api.setNAME_ROAD("rue des universites");
//        api.setPART_OF_DAY("matin");
//        api.setWEATHER_ICONS("Soleille");
//        api.setSEASON("printemp");
//        api.setTYPE_TEMPRETURE("C");
//        api.setVALUE(1);
//        api.setWEATHER("Sunny");
//        api.setYEARS(1999);
//        
//        dapi.insertAnnotationPhotoInfo(api);
//        System.out.println(dapi.getNameCountry("FR"));
//     }
}
